package io.finer.erp.jeecg.stock.mapper;

import java.util.List;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import io.finer.erp.jeecg.stock.entity.ErpSysUser;
import io.finer.erp.jeecg.stock.entity.IoEntryDTO;
import io.finer.erp.jeecg.stock.entity.SaleOrderDTO;
import io.finer.erp.jeecg.stock.entity.StkPickUpEntry;
import io.finer.erp.jeecg.stock.vo.CompanyModel;
import io.finer.erp.jeecg.stock.vo.InventoryVo;

public interface ScanMapper {
	@Select("select s.item_value as \"value\",s.item_text as \"name\" from sys_dict_item s where dict_id = (select id from sys_dict where dict_code = #{code}) order by s.sort_order asc")
	List<CompanyModel> getCompany(@Param("code")String code);
	/**
	 * @Description 获取代加工库中的物料 id为库存调拨明细的主键 name为物料名称 code 为物料编码
	 * @return
	 * Create at: 2020-11-02 17:37:01
	 * @author: duany
	 * Revision:
	 *    2020-11-02 17:37:01 - first revision by duany
	 *        
	 */
	@Select("select entry.id, mate.name,mate.code from stk_io_bill_entry entry LEFT JOIN bas_material mate on entry.material_id=mate.id WHERE mid in (select id from stk_io_bill WHERE stock_io_type='301' and is_approved=1) and entry.in_warehouse_id='1315841495892500482' order by id desc LIMIT 0,200")
	List<InventoryVo> getProcessingOrder();
	
	/**
	 * @Description 查询是生产班长的用户
	 * @return
	 * Create at: 2020-11-28 12:55:11
	 * @author: duany
	 * Revision:
	 *    2020-11-28 12:55:11 - first revision by duany
	 *        
	 */
	@Select("SELECT * FROM sys_user WHERE id IN (SELECT user_id FROM sys_user_role WHERE role_id IN (select id FROM sys_role WHERE role_code='Inspector'))")
	List<ErpSysUser> getInspector();
	
	@Delete("delete from stk_io_bill_entry where material_id not in (select id from bas_material) and mid not in (select id from stk_io_bill where stock_io_type = '601')")
	int delNotMaterialIoBillEntry();
	
	@Delete("delete from stk_inventory where material_id not in (select id from bas_material)")
	int delNotMaterialInventory();
	
	//根据物料ID和MES单ID查询出入库明细
	@Select("select entry.id as entryId,entry.material_id as materialId, entry.cost, entry.qty,bill.create_time,bill.stock_io_type as ioType,bill.bill_no as billNo,bill.id as billId from stk_io_bill bill LEFT JOIN stk_io_bill_entry entry ON bill.id = entry.mid where bill.source_id=#{mesId} and entry.material_id=#{materialId} ORDER BY bill.create_time DESC")
	List<IoEntryDTO> getStkIoBillEntryByMesMaterial(@Param("materialId")String materialId, @Param("mesId")String mesId);
	
	/**
	 * @Description 根据物料分类ID查询销售订单
	 * @return
	 * Create at: 2021-03-02 14:54:32
	 * @author: duany
	 * Revision:
	 *    2021-03-02 14:54:32 - first revision by duany
	 *        
	 */
	@Select("select id,id as orderId,customer_name as customerName,customer_id as customerId,order_no as orderNo from zrk_shoporder where id in (select zrk_order_product.order_id from zrk_order_product where zrk_order_product.material_id=#{categoryId}) order by id desc LIMIT 200")
	List<SaleOrderDTO> getZrkShoporderByCategoryId(@Param("categoryId")String categoryId);
	
	/**
	 * @Description 根据物料分类ID查询销售订单
	 * @return
	 * Create at: 2021-03-02 14:54:32
	 * @author: duany
	 * Revision:
	 *    2021-03-02 14:54:32 - first revision by duany
	 *        
	 */
	@Select("select id,id as orderId,customer_name as customerName,customer_id as customerId,order_no as orderNo from zrk_shoporder where id in (select zrk_order_product.order_id from zrk_order_product where zrk_order_product.material_id=#{categoryId}) and customer_id = #{customerId} order by id desc LIMIT 200")
	List<SaleOrderDTO> getZrkShoporderByCategoryIdAndCustomer(@Param("categoryId")String categoryId, @Param("customerId")String customerId);
	
	/**
	 * @Description 根据物料分类ID集合查询销售订单
	 * @param categoryIds
	 * @return
	 * Create at: 2021-03-03 08:55:33
	 * @author: duany
	 * Revision:
	 *    2021-03-03 08:55:33 - first revision by duany
	 *        
	 */
	List<SaleOrderDTO> getZrkShoporderByCategoryIds(@Param("categoryIds")String categoryIds, @Param("customerIds")String customerIds);
	
	/**
	 * @Description 根据mes单查询提货单明细
	 * @param mesId
	 * @return
	 * Create at: 2021-03-10 14:53:10
	 * @author: duany
	 * Revision:
	 *    2021-03-10 14:53:10 - first revision by duany
	 *        
	 */
	@Select("select * from stk_pick_up_entry where bill_id in (select id from stk_pick_up_bill where mes_id=#{mesId})")
	List<StkPickUpEntry> getPickUpEntryByMes(@Param("mesId")String mesId);
}
